﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using Elderos.Utils.Logging;
using Elderos.Utils.SQL;

namespace Elderos.Ontology
{
    public class OntologyData : IOntologyData
    {
        private readonly Dictionary<string, double> _synonymFrequencyCache = new Dictionary<string, double>();
        private const int SynonymFrequencyCacheSize = 15000;
        private readonly ReaderWriterLockSlim _synonymFreqLocker = new ReaderWriterLockSlim();

        private readonly Dictionary<Tuple<int, int>, List<AttValue>> _attValueCache = new Dictionary<Tuple<int, int>, List<AttValue>>();
        private const int AttValueCacheSize = 15000;
        private readonly ReaderWriterLockSlim _attValueCacheLocker = new ReaderWriterLockSlim();

        private readonly Dictionary<Tuple<int, int>, List<AttRef>> _attRefCache = new Dictionary<Tuple<int, int>, List<AttRef>>();
        private const int AttRefCacheSize = 15000;
        private readonly ReaderWriterLockSlim _attRefCacheLocker = new ReaderWriterLockSlim();

        private readonly Dictionary<int, List<EntityFBType>> _entityFBTypeCache = new Dictionary<int, List<EntityFBType>>();
        private const int EntityFBTypeCacheSize = 15000;
        private readonly ReaderWriterLockSlim _entityFBTypeLocker = new ReaderWriterLockSlim();

        public const string TokenizerPattern = @"(?:\w+|\W)";

        public List<Attribute> Attributes { get; private set; }

        public List<FreebaseType> Types { get; private set; }

        public HashSet<string> CommonWords { get; private set; }
        public HashSet<string> PreNames
        {
            get { return _preNames; }
            private set { _preNames = value; }
        }

        public Dictionary<string, int> Trigrams { get; private set; }

        public Dictionary<string, DictionaryWord> WordsDictionary { get; private set; }

        private string _connectionString;

        public OntologyData(string connectionString)
        {
            Logger.Assert<ArgumentNullException>(() => connectionString != null, "Connection string cannot be null");
            _connectionString = connectionString;
        }

        public bool Initialized { get; private set; }
        private object _initLocker = new object();
        private HashSet<string> _preNames = new HashSet<string>(StringComparer.InvariantCultureIgnoreCase)
                                                {
                                                    "van",
                                                    "von",
                                                    "de",
                                                    "d",
                                                    "di",
                                                    "del",
                                                    "da",
                                                    "'",
                                                    "`",
                                                    "der",
                                                    "ibn",
                                                    "O",
                                                    "l",
                                                    "le",
                                                    "la",
                                                    "les",
                                                    "of",
                                                    "the"
                                                };

        public void Initialize()
        {
            if (Initialized) return;

            lock (_initLocker)
            {
                Logger.Info("Initializing Ontology...");
                if (Initialized) return;
                Attributes = GetAttributes();
                Types = GetFreebaseTypes();
                CommonWords = new HashSet<string>(GetCommonWords());

                WordsDictionary = GetWordsDictionary().ToDictionary(x => x.Word, x => x);

                var trigrams = GetTrigrams();
                Trigrams = trigrams.GroupBy(x => x).ToDictionary(x => x.Key, x => x.Count());

                Initialized = true;
                Logger.Info("Ontology initialized.");
            }
        }

        public IEnumerable<SynonymEntityDTO> GetFBTypeSynonyms(IEnumerable<string> fbtypes)
        {
            DataTable dt = CreateSingleList(fbtypes.Select(x => Types.First(y => y.Name == x).ID));

            using (var connection = new SqlConnection(_connectionString))
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = "SelectTypeSynonyms";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 60000;
                cmd.Parameters.Add(new SqlParameter("types", SqlDbType.Structured) {Value = dt});

                connection.Open();
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var synonymEntityDTO = new SynonymEntityDTO
                            {
                                EntityID = reader.GetInt32(0),
                                Synonym = reader.GetString(1),
                                SynonymID = reader.GetInt32(2)
                            };
                        yield return synonymEntityDTO;
                    }
                }
            }
        }

        public IEnumerable<NameSynonym> GetNameSynonyms()
        {
            using (var connection = new SqlConnection(_connectionString))
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = "select Name, Normalized from NameSynonyms";

                connection.Open();
                using (var reader = cmd.ExecuteReader())
                while (reader.Read())
                {
                    var nameSynonym = new NameSynonym();
                    nameSynonym.Name = reader.GetString(0);
                    nameSynonym.Normalized = reader.GetString(1);
                    yield return nameSynonym;
                }
            }
        }

        public async Task<List<AttValue>> GetAttValuesAsync(IEnumerable<int> attributeIDs, IEnumerable<int> entityIDs)
        {
            var inCache = new List<AttValue>();

            var atts = new HashSet<int>(attributeIDs);

            var notInCache = new List<int>();

            foreach (int entityID in entityIDs)
            {
                foreach (var att in atts)
                {
                    try
                    {
                        _attValueCacheLocker.EnterReadLock();
                        List<AttValue> cached;
                        if (_attValueCache.TryGetValue(new Tuple<int, int>(entityID, att), out cached))
                            inCache.AddRange(cached.Where(x => atts.Contains(x.AttID)));
                        else
                            notInCache.Add(entityID);
                    }
                    finally
                    {
                        _attValueCacheLocker.ExitReadLock();
                    }
                }
            }

            if (notInCache.Count < 1) return inCache;
            List<AttValue> inBase = await GetAttValuesFromDbAsync(atts, notInCache);

            if (_attValueCache.Count < AttValueCacheSize)
            {
                try
                {
                    _attValueCacheLocker.EnterWriteLock();
                    foreach (var attValue in inBase.GroupBy(x => new Tuple<int, int>(x.EntityID, x.AttID)))
                    {
                        var key = attValue.Key;
                        if (!_attValueCache.ContainsKey(key))
                        {
                            _attValueCache.Add(key, new List<AttValue>(attValue));
                        }
                    }
                }
                finally
                {
                    _attValueCacheLocker.ExitWriteLock();
                }
            }

            var result = new List<AttValue>();
            result.AddRange(inCache);
            foreach (var attValue in inBase)
            {
                if (result.All(x => x.ID != attValue.ID))
                    result.Add(attValue);
            }

            return result;
        }

        public async Task<List<AttRef>> GetAttRefsAsync(IEnumerable<int> attributeIDs, IEnumerable<int> entityIDs)
        {
            var inCache = new List<AttRef>();

            var atts = new HashSet<int>(attributeIDs);

            var notInCache = new List<int>();

            foreach (int entityID in entityIDs)
            {
                foreach (var att in atts)
                {
                    try
                    {
                        _attRefCacheLocker.EnterReadLock();
                        List<AttRef> cached;
                        if (_attRefCache.TryGetValue(new Tuple<int, int>(entityID, att), out cached))
                            inCache.AddRange(cached.Where(x => atts.Contains(x.AttID)));
                        else
                            notInCache.Add(entityID);
                    }
                    finally
                    {
                        _attRefCacheLocker.ExitReadLock();
                    }
                }
            }

            if (notInCache.Count < 1) return inCache;
            List<AttRef> inBase = await GetAttRefsFromDbAsync(atts, notInCache);

            if (_attRefCache.Count < AttRefCacheSize)
            {
                try
                {
                    _attRefCacheLocker.EnterWriteLock();
                    foreach (var attRef in inBase.GroupBy(x => new Tuple<int, int>(x.EntityID, x.AttID)))
                    {
                        var key = attRef.Key;
                        if (!_attRefCache.ContainsKey(key))
                        {
                            _attRefCache.Add(key, new List<AttRef>(attRef));
                        }
                    }
                }
                finally
                {
                    _attRefCacheLocker.ExitWriteLock();
                }
            }

            var result = new List<AttRef>();
            result.AddRange(inCache);
            foreach (var attRef in inBase)
            {
                if (result.All(x => x.ID != attRef.ID))
                    result.Add(attRef);
            }

            return result;
        }

        public async Task<List<AttRef>> GetAttRefsAsync(IEnumerable<int> attributeIDs)
        {
            return await GetAttRefsFromDbAsync(attributeIDs);
        }


        public async Task<List<EntityFBType>> GetEntityFBTypesAsync(IEnumerable<int> entityIDs)
        {
            var notInCache = new List<int>();
            var inCache = new List<EntityFBType>();
            try
            {
                _entityFBTypeLocker.EnterReadLock();
                foreach (int entityID in entityIDs)
                {
                    List<EntityFBType> cached;
                    if (!_entityFBTypeCache.TryGetValue(entityID, out cached))
                        notInCache.Add(entityID);
                    else
                        inCache.AddRange(cached);
                }
            }
            finally
            {
                _entityFBTypeLocker.ExitReadLock();
            }

            if (notInCache.Count < 1) return inCache;
            var inBase = await GetEntityFBTypesFromDbAsync(notInCache);

            if (_entityFBTypeCache.Count < EntityFBTypeCacheSize)
            {
                try
                {
                    _entityFBTypeLocker.EnterWriteLock();
                    foreach (var grouping in inBase.GroupBy(x => x.EntityID))
                    {
                        if (!_entityFBTypeCache.ContainsKey(grouping.Key))
                            _entityFBTypeCache.Add(grouping.Key, new List<EntityFBType>(grouping));
                    }
                }
                finally
                {
                    _entityFBTypeLocker.ExitWriteLock();
                }
            }

            var result = new List<EntityFBType>(inCache);
            foreach (var entityFBType in inBase)
            {
                if (inCache.All(x => x.ID != entityFBType.ID))
                    result.Add(entityFBType);
            }
            return result;
        }

        public async Task<List<Synonym>> GetSynonymsAsync(IEnumerable<int> synonymIDs)
        {
            return await GetSynonymsFromDbAsync(synonymIDs);
        }

        public async Task<Dictionary<string, double>> GetSynonymFrequenciesAsync(IEnumerable<string> synonyms)
        {
            var result = new Dictionary<string, double>();

            var notInCache = new HashSet<string>();
            foreach (string synonym in synonyms)
            {
                if (result.ContainsKey(synonym)) continue;

                double frequency;
                try
                {
                    _synonymFreqLocker.EnterReadLock();
                    if (_synonymFrequencyCache.TryGetValue(synonym, out frequency))
                        result.Add(synonym, frequency);
                    else
                        notInCache.Add(synonym);
                }
                finally
                {
                    _synonymFreqLocker.ExitReadLock();
                }
            }

            if (notInCache.Count < 1) return result;

            IEnumerable<SynonymFrequency> notInCacheFrequency = await GetSynonymFrequenciesFromDb(notInCache);

            foreach (SynonymFrequency synonymFrequency in notInCacheFrequency)
            {
                string synonym = synonymFrequency.Synonym;
                double frequency = synonymFrequency.Frequency;

                try
                {
                    _synonymFreqLocker.EnterWriteLock();
                    if (_synonymFrequencyCache.Count < SynonymFrequencyCacheSize && !_synonymFrequencyCache.ContainsKey(synonym))
                        _synonymFrequencyCache.Add(synonym, frequency);
                }
                finally
                {
                    _synonymFreqLocker.ExitWriteLock();
                }

                result.Add(synonym, frequency);
            }

            return result;
        }

        public List<Attribute> GetAttributes()
        {
            var result = new SqlQuery(_connectionString).ExecuteList(
                "select * from Attributes",
                reader =>
                    {
                        var att = new Attribute();
                        att.ID = reader.GetField<int>("ID");
                        att.IsReference = reader.GetField<bool>("IsReference");
                        att.Name = reader.GetField<string>("Name");
                        return att;
                    }
                );

            return result;
        }

        public List<FreebaseType> GetFreebaseTypes()
        {
            var result = new SqlQuery(_connectionString).ExecuteList(
                @"select * from FreebaseTypes",
                reader =>
                    {
                        var fbType = new FreebaseType();
                        fbType.ID = reader.GetField<int>("ID");
                        fbType.Name = reader.GetField<string>("Name");
                        return fbType;
                    }
                );

            return result;
        }

        public List<string> GetCommonWords()
        {
            var result = new SqlQuery(_connectionString).ExecuteList(
                @"select word from CommonWords",
                reader => reader.GetString(0));

            return result;
        }

        public List<string> GetTrigrams()
        {
            var result = new SqlQuery(_connectionString).ExecuteList(
                @"select triple from Trigrams",
                reader => reader.GetString(0)
                );

            return result;
        }

        public Entity GetEntity(int entityID)
        {
            var result = new SqlQuery(_connectionString).ExecuteSingle(
                @"select * from Entities where EntityID = @entityID",
                ReadEntity,
                new SqlParameter("entityID", entityID)
                );

            return result;
        }

        public async Task<Entity> GetEntityAsync(int entityID)
        {
            var result = await new SqlQuery(_connectionString).ExecuteSingleAsync(
                @"select * from Entities where EntityID = @entityID",
                ReadEntity,
                new SqlParameter("entityID", entityID)
                );

            return result;
        }

        private static Entity ReadEntity(IDataReader reader)
        {
            var entity = new Entity();
            entity.EntityID = reader.GetField<int>("EntityID");
            entity.MID = reader.GetField<string>("MID");
            entity.Name = reader.GetField<string>("Name");
            return entity;
        }

        private static AttValue ReadAttValue(IDataReader reader)
        {
            var attValue = new AttValue();
            attValue.ID = reader.GetField<int>("ID");
            attValue.EntityID = reader.GetField<int>("EntityID");
            attValue.AttID = reader.GetField<int>("AttID");
            attValue.Value = reader.GetField<string>("Value");
            return attValue;
        }

        private static AttRef ReadAttRef(IDataReader reader)
        {
            var attRef = new AttRef();
            attRef.ID = reader.GetField<int>("ID");
            attRef.EntityID = reader.GetField<int>("EntityID");
            attRef.AttID = reader.GetField<int>("AttID");
            attRef.ReferenceID = reader.GetField<int>("ReferenceID");
            return attRef;
        }

        private static EntityFBType ReadEntityFBTypes(IDataReader reader)
        {
            var fbType = new EntityFBType();
            fbType.ID = reader.GetField<int>("ID");
            fbType.EntityID = reader.GetField<int>("EntityID");
            fbType.FBTypeID = reader.GetField<int>("FBTypeID");
            return fbType;
        }


        public async Task<List<AttValue>> GetAttValuesFromDbAsync(IEnumerable<int> attributeIDs, IEnumerable<int> entityIDs)
        {
            DataTable attributes = CreateSingleList(attributeIDs);
            DataTable entities = CreateSingleList(entityIDs);

            return await new SqlQuery(_connectionString).ExecuteListAsync(
                new SqlCommand("SelectEntityAttValues") {CommandType = CommandType.StoredProcedure},
                ReadAttValue,
                new SqlParameter("@attIDs", SqlDbType.Structured) {Value = attributes},
                new SqlParameter("@entityIDs", SqlDbType.Structured) {Value = entities}
                );
        }

        public async Task<List<AttRef>> GetAttRefsFromDbAsync(IEnumerable<int> attributeIDs, IEnumerable<int> entityIDs)
        {
            DataTable attributes = CreateSingleList(attributeIDs);
            DataTable entities = CreateSingleList(entityIDs);

            return await new SqlQuery(_connectionString).ExecuteListAsync(
                new SqlCommand("SelectEntityAttRefs") {CommandType = CommandType.StoredProcedure},
                ReadAttRef,
                new SqlParameter("@attIDs", SqlDbType.Structured) {Value = attributes},
                new SqlParameter("@entityIDs", SqlDbType.Structured) {Value = entities}
                );
        }

        public async Task<List<EntityFBType>> GetEntityFBTypesFromDbAsync(IEnumerable<int> entityIDs)
        {
            DataTable entities = CreateSingleList(entityIDs);

            return await new SqlQuery(_connectionString).ExecuteListAsync(
                new SqlCommand("SelectEntityFBTypes"){CommandType = CommandType.StoredProcedure},
                ReadEntityFBTypes,
                new SqlParameter("@entityIDs", SqlDbType.Structured) { Value = entities }
                );
        }

        private static Network ReadNetwork(IDataReader reader)
        {
            var network = new Network();
            network.Comment = reader.GetField<string>("Comment");
            network.CreationDate = reader.GetField<DateTime>("CreationDate");
            network.LearningTime = reader.GetField<double>("LearningTime");
            network.NetworkData = reader.GetField<byte[]>("Network");
            network.NetworkID = reader.GetField<int>("NetworkID");
            network.NormalizerData = reader.GetField<byte[]>("Normalizer");
            network.Precision = reader.GetField<double>("Precision");
            network.Recall = reader.GetField<double>("Recall");
            network.Threshold = reader.GetField<double>("Threshold");
            network.TrainingSetPrecision = reader.GetField<double>("TrainingSetPrecision");
            network.TrainingSetRecall = reader.GetField<double>("TrainingSetRecall");
            network.TrainingSetThreshold = reader.GetField<double>("TrainingSetThreshold");
            return network;
        }

        public Network GetNetwork(int networkID)
        {
            return new SqlQuery(_connectionString).ExecuteSingle(
                                          @"select * from Networks where NetworkID = @networkID",
                                          ReadNetwork,
                                          new SqlParameter("networkID", networkID));
        }

        public int InsertNetwork(Network network)
        {
            int networkID = new SqlQuery(_connectionString).ExecuteSingle(

@"insert into Networks(Comment, CreationDate, LearningTime, Network,
                       Normalizer, Precision, Recall, Threshold,
                       TrainingSetPrecision, TrainingSetRecall, TrainingSetThreshold)
values (@comment, @creationDate, @learningTime, @network,
        @normalizer, @precision, @recall, @threshold,
        @trainingSetPrecision, @trainingSetRecall, @trainingSetThreshold)

select @@identity;
",
                reader => reader.GetInt32(0),
                new SqlParameter("comment", network.Comment),
                new SqlParameter("creationDate", network.CreationDate),
                new SqlParameter("learningTime", network.LearningTime),
                new SqlParameter("network", network.NetworkData),
                new SqlParameter("normalizer", network.NormalizerData),
                new SqlParameter("precision", network.Precision),
                new SqlParameter("recall", network.Recall),
                new SqlParameter("threshold", network.Threshold),
                new SqlParameter("trainingSetPrecision", network.TrainingSetPrecision),
                new SqlParameter("trainingSetRecall", network.TrainingSetRecall),
                new SqlParameter("trainingSetThreshold", network.TrainingSetThreshold)
                );
            return networkID;
        }

        public void UpdateNetwork(Network network)
        {
            new SqlQuery(_connectionString).ExecuteCmd(

@"update Networks
set Comment = @comment,
    CreationDate = @creationDate,
    LearningTime = @learningTime,
    Network = @network,
    Normalizer = @normalizer,
    Precision = @precision,
    Recall = @recall,
    Threshold = @threshold,
    TrainingSetPrecision = @trainingSetPrecision,
    TrainingSetRecall = @trainingSetRecall,
    TrainingSetThreshold = @trainingSetThreshold
where NetworkID = @networkID
",
                new SqlParameter("networkID", network.NetworkID),
                new SqlParameter("comment", network.Comment),
                new SqlParameter("creationDate", network.CreationDate),
                new SqlParameter("learningTime", network.LearningTime),
                new SqlParameter("network", network.NetworkData),
                new SqlParameter("normalizer", network.NormalizerData),
                new SqlParameter("precision", network.Precision),
                new SqlParameter("recall", network.Recall),
                new SqlParameter("threshold", network.Threshold),
                new SqlParameter("trainingSetPrecision", network.TrainingSetPrecision),
                new SqlParameter("trainingSetRecall", network.TrainingSetRecall),
                new SqlParameter("trainingSetThreshold", network.TrainingSetThreshold)
                );
        }

        public void DeleteNetwork(int networkID)
        {
            new SqlQuery(_connectionString).ExecuteCmd(

                @"delete from Networks where NetworkID = @networkID",
                new SqlParameter("networkID", networkID)
                );
        }

        private DataTable CreateSingleList<T>(IEnumerable<T> obj, string columnName = "Value")
        {
            DataTable table = CreateDataTable(new Dictionary<string, Type> { { columnName, typeof(T) } });
            FillSingleTable(table, obj, columnName);
            return table;
        }

        private void FillSingleTable<T>(DataTable table, IEnumerable<T> list, string columnName = "Value")
        {
            foreach (T item in list)
            {
                DataRow row = table.NewRow();
                row[columnName] = item;
                table.Rows.Add(row);
            }
        }

        private DataTable CreateDataTable(IEnumerable<KeyValuePair<string, Type>> columnValues)
        {
            var table = new DataTable();
            foreach (var columnValue in columnValues)
            {
                table.Columns.Add(columnValue.Key, columnValue.Value);
            }

            return table;
        }

        public async Task<List<AttRef>> GetAttRefsFromDbAsync(IEnumerable<int> attributeIDs)
        {
            DataTable attributes = CreateSingleList(attributeIDs);
            return await new SqlQuery(_connectionString).ExecuteListAsync(
                new SqlCommand("SelectAttRefs"){CommandType = CommandType.StoredProcedure},
                ReadAttRef,
                new SqlParameter("@attIDs", SqlDbType.Structured) {Value = attributes}
                );
        }

        private static Synonym ReadSynonym(IDataReader reader)
        {
            int synonymID = reader.GetField<int>("ID");
            string syno = reader.GetField<string>("Syno");
            var synonym = new Synonym(synonymID, syno);
            return synonym;
        }

        

        public async Task<List<Synonym>> GetSynonymsFromDbAsync(IEnumerable<int> synonymIDs)
        {
            DataTable attributes = CreateSingleList(synonymIDs);

            return await new SqlQuery(_connectionString).ExecuteListAsync(
                new SqlCommand("SelectSynonyms")
                    {
                        CommandType = CommandType.StoredProcedure
                    },
                ReadSynonym,
                new SqlParameter("@synonymIDs", SqlDbType.Structured) {Value = attributes}
                );
        }

        private static SynonymFrequency ReadSynonymFrequency(IDataReader reader)
        {
            var sf = new SynonymFrequency();
            sf.Synonym = reader.GetField<string>("Syno");
            sf.Frequency = reader.GetField<double>("Frequency");
            return sf;
        }

        public async Task<List<SynonymFrequency>> GetSynonymFrequenciesFromDb(HashSet<string> synonyms)
        {
            DataTable dt = CreateSingleList(synonyms.Distinct());

            return await new SqlQuery(_connectionString).ExecuteListAsync(
                new SqlCommand("SelectSynonymFrequencies"){CommandType = CommandType.StoredProcedure},
                ReadSynonymFrequency,
                new SqlParameter("@synonyms", SqlDbType.Structured) {Value = dt}
                );
        }

        public List<DictionaryWord> GetWordsDictionary()
        {
            return new SqlQuery(_connectionString).ExecuteList(

                "select * from Words",
                reader =>
                    {
                        var word = new DictionaryWord();
                        word.Word = reader.GetField<string>("Word");
                        word.TextFrequency = reader.GetField<double>("TextFrequency");
                        word.WordFrequency = reader.GetField<double>("WordFrequency");
                        return word;
                    }
                );
        }

        public List<string> GetNames()
        {
            return new SqlQuery(_connectionString).ExecuteList(

                "select Name from dbo.Names",
                reader => reader.GetString(0)
                );
        }

        public List<string> GetLastNames()
        {
            return new SqlQuery(_connectionString).ExecuteList(

                "select Name from dbo.LastNames",
                reader => reader.GetString(0)
                );
        }


        public IEnumerable<SynonymEntityDTO> LoadSynonyms()
        {
            using (var connection = new SqlConnection(_connectionString))
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandTimeout = 600;

//        select s.ID, s.Syno, se.EntityID
//from dbo.Synonyms s
//inner join SynonymEntities se on se.synoid = s.id and se.isActive = 1
//inner join Entities e on e.EntityID = se.EntityID
//inner join States st on st.id = e.state and st.isactive = 1
//--where s.Frequency > 0
//order by s.ID

                cmd.CommandText = @"select *
from SynonymsPreloaded";
                
                connection.Open();
                using (IDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int synoID = reader.GetInt32(0);
                        string syno = reader.GetString(1);
                        int entityID = reader.GetInt32(2);
                        yield return new SynonymEntityDTO() { SynonymID = synoID, EntityID = entityID, Synonym = syno };
                    }
                }
            }
        }

        public int EstimatedSynonymCount
        {
            get
            {
//                select count(*)
//from dbo.Synonyms s
//inner join SynonymEntities se on se.synoid = s.id and se.isActive = 1
//inner join Entities e on e.EntityID = se.EntityID
//inner join States st on st.id = e.state and st.isactive = 1
                string cmdText =
@"select count(*)
from SynonymsPreloaded";

                return new SqlQuery(_connectionString).ExecuteSingle(
                    new SqlCommand(cmdText) {CommandTimeout = 600},
                    reader => reader.GetInt32(0)
                    );
            }
        }
    }
}
